﻿using Newtonsoft.Json.Linq;
using Org.BouncyCastle.Asn1.Ocsp;
using QQZiFramework;
using QQZiFramework.Membership;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Data;

namespace TestProject1
{
    [TestClass]
    public class TestDBContext
    {
        DBContext Context;
        public TestDBContext()
        {
            // var _OEMProgram = new OEMProgram("DMTEST");
            var _OEMProgram = new OEMProgram("JPAAS");
            _OEMProgram.Run(null);
            Context = _OEMProgram.CreateDBContext();


        }

        [TestMethod]
        public void IsTableExists()
        {
            var result = Context.GetDBAccess().IsTableExists("AMIS_SLN_DEFINITION");
            Assert.IsTrue(result);
            var columnExists = Context.GetDBAccess().IsColumnExists("AMIS_SLN_DEFINITION", "SLN");
            Assert.IsTrue(columnExists);
            columnExists = Context.GetDBAccess().IsColumnExists("AMIS_SLN_DEFINITION", "SLN2");
            Assert.IsFalse(columnExists);
        }

        [TestMethod]
        public void TestTree()
        {
            QueryParam param = new QueryParam();
            var treeObj = Context.QueryTree<SystemDept>(param);
            Assert.IsTrue(treeObj.Count > 0);
            var category = Context.QuerySingle<SystemDept>(new { Name = "上海三高" });
            treeObj = Context.QueryTree<SystemDept>(param, category.Id);
            Assert.IsTrue(treeObj.Count > 0);
            Assert.IsTrue(treeObj[0].Id > category.Id);
            Assert.IsTrue(treeObj[0].Children.Count > 0);
            param.WhereGt("Id", 5);
            treeObj = Context.QueryTree<SystemDept>(param);
            Assert.IsTrue(treeObj.Count > 0);
            Assert.IsTrue(treeObj[0].Id == 6);
            Assert.IsTrue(treeObj[0].Children.Count > 0);
        }

         [TestMethod]
        public void TestExists()
        {
           var exists= Context.Exists<PermissionSlutionDBData>(new { Id = 1});
           
        }

        [TestMethod]
        public void TestIn()
        {
            var lst = Context.QueryMany<SystemDept>(new { IdIn = "0,1,2,3" }, "system-dept", null);//, CreateTime = "2020-01-01|2022-02-02"
            // Assert.IsTrue(Context.GetDBAccess().GetLastSql().IndexOf("'0','1','2','3'") > 0);
            lst = Context.QueryMany<SystemDept>(new { IdIn = "'0','1','2','3'", CreateTime = "2020-01-01|2022-02-02" }, "system-dept", null);
            // Assert.IsTrue(Context.GetDBAccess().GetLastSql().IndexOf("'0','1','2','3'") > 0);
            Context.QueryMany<SystemDept>(new { groupby = 1, Id__Custom = WhereItem_Custom.Create("in ({0})", "1,3,4") }, "system-dept", "MaxId");
            // Assert.IsTrue(Context.GetDBAccess().GetLastSql().IndexOf("Id in ('1','3','4')") > 0);
            Context.QueryMany<SystemDept>(new { groupby = 1, Id__Custom = WhereItem_Custom.Create("in (1,3,4,5,6,7)") }, "system-dept", "MaxId");
            Context.QueryMany<SystemDept>(new { groupby = 1, Id__Custom = WhereItem_Custom.Create("${field} >{0} and ${field} not in (1,2,3)", -1) }, "system-dept", "MaxId");
            Context.QueryMany<SystemDept>(new { Names = "tome,nick,lily" }, "system-dept", null);//, CreateTime = "2020-01-01|2022-02-02"
            // Assert.IsTrue(Context.GetDBAccess().GetLastSql().IndexOf("Name in ('tome','nick','lily'") > 0);
        }

        /// <summary>
        /// concat_template自定义where拼接测试
        /// </summary>
        [TestMethod]
        public void TestWhereConcatTemplate()
        {
            var list = Context.QueryMany<SystemDept>(new { IdBetween = "0|100", Selled__LT = 1 }, "ShopCategories", null);//, CreateTime = "2020-01-01|2022-02-02"
            Console.WriteLine(Context.GetDBAccess().GetLastSql());
            Assert.IsNotNull(list);
            Assert.IsTrue(list.Count > 0);

        }

        [TestMethod]
        public void TestQueryBuilder()
        {
            Context.QuerySingle<PermissionSlutionDBData>(new { OEM = "JPAAS", SLN__Custom = QQZiFramework.WhereItem_Custom.Create("$field = {0} or Permission ={1}", "856c5cbd5da64450826c9d3027526a27", "856c5cbd5da64450826c9d3027526a27") });

            var firstPage = Context.QueryTopOne<DesignPage>(new { });
            

            //包含or条件  a =1 and b=2 and (c =3 or c is null)
            var list = Context.CreateQueryBuilder()
                .Select("*")
                .OrderBy("Id")
                .From<SystemDept>()
                .Like("Name", "家装")   //外层默认and 条件 
                .NotNull("Name")
                .Or().Between("Id", 1, 1000).Equal("Id", 0).endOr()   //生成or子条件组 and (Id between 1 and 1000 OR Id = 0 )
                .QueryMany();

            // Assert.IsTrue(Context.GetDBAccess().GetLastSql().IndexOf("(Id between @Id1 and @Id2) or (Id =") > 0);

            //外层or条件组 , 内层再加and条件组，比如   a =1 or b=2 or (c=1 and c is null)
            var lst2 = Context.CreateQueryBuilder()
            .Select("*")
            .From(typeof(SystemDept))
            .Or().NotEqual("Id", 0)   //外层用or子条件组
                .And().NotNull("Name").StartWith("Name","上海").EndAnd()   //生成and子条件组 or（Name is not null and Name lime '%家装%' ）
            .endOr()
            .QuerySingle();

            // Assert.IsTrue(Context.GetDBAccess().GetLastSql().IndexOf("((Id <> @Id) or ((Name is not null) and (Name like concat('%',@Name,'%')))) and ((Status <> @Status) or (Status is null))") > 1);

         
        }


        [TestMethod]
        public void TestRewriteTable()
        {
            Context.RewriteTableAttr<EmptyEntity>(new DBTableAttribute() { TableName = "A3_ShopCategories" });
            var list = Context.CreateQueryBuilder().Permission("ShopCategories").Select("Id,Name").From(typeof(EmptyEntity)).Equal("IdIn", "1,2,3,4,5,10,100").QueryMany();
            Assert.IsNotNull(list.Count > 0);
        }

        [TestMethod]
        public void TestConditionGroupJson()
        {
            String json = "{\"conjunction\":\"and\",\"children\":[{\"left\":{\"type\":\"field\",\"field\":\"Name\"},\"op\":\"like\",\"right\":\"家装\"},{\"conjunction\":\"or\",\"children\":[{\"left\":{\"type\":\"field\",\"field\":\"CreateTime\"},\"op\":\"between\",\"right\":[\"2023-05-26\",\"2023-05-27\"]},{\"left\":{\"type\":\"Field\",\"field\":\"Id\"},\"op\":\"greater\",\"right\":\"0\"},{\"left\":{\"type\":\"Field\",\"field\":\"Id\"},\"op\":\"equal\",\"right\":\"0\"},{\"left\":{\"type\":\"Field\",\"field\":\"Id\"},\"op\":\"is_not_empty\"}]}]}";
            QueryParamConditionGroup conditionGroup = ConvertConditionJsonToQueryParamConditionGroup(JToken.Parse(json));
            // Context.RewriteTableAttr<EmptyEntity>(new DBTableAttribute() { TableName = "A3_ShopCategories" });
            // QueryParamConditionGroup conditionGroup = new QueryParamConditionGroup() { Conjunction = QueryParamConditionGroup.ConditionGroupConjunction.and };
            // conditionGroup.Items.Add(new QueryParamConditionItem("Id", new WhereItem_In(new object[] { 1, 2, 3, 4 })));
            // QueryParamConditionGroup conditionGroup2 = new QueryParamConditionGroup() { Conjunction = QueryParamConditionGroup.ConditionGroupConjunction.or };
            // conditionGroup2.Items.Add(new QueryParamConditionItem("CreateTime", new WhereItem_Eq("2021-01-01|2023-01-01")));
            // conditionGroup2.Items.Add(new QueryParamConditionItem("Name", new WhereItem_Custom("Name is null")));
            // conditionGroup.Groups.Add(conditionGroup2);
            var dt = Context.CreateQueryBuilder().Permission("ShopCategories").Select("Id,Name").From(typeof(EmptyEntity)).Condition(conditionGroup).Query();
            Assert.IsTrue(Context.GetDBAccess().GetLastSql().IndexOf("Name like concat('%',@Name,'%')") > 0);
            Assert.IsTrue(Context.GetDBAccess().GetLastSql().IndexOf("(CreateTime between @CreateTime1 and @CreateTime2) or (Id > @Id1)") > 0);

        }


        private QueryParamConditionGroup ConvertConditionJsonToQueryParamConditionGroup(JToken jToken)
        {
            QueryParamConditionGroup conditionGroup = new QueryParamConditionGroup();
            conditionGroup.Conjunction = jToken.Value<string>("conjunction") == "or" ? QueryParamConditionGroup.ConditionGroupConjunction.or : QueryParamConditionGroup.ConditionGroupConjunction.and;
            foreach (JToken child in jToken["children"])
            {
                if (child["children"] != null && child["children"].HasValues)
                {
                    conditionGroup.Groups.Add(ConvertConditionJsonToQueryParamConditionGroup(child));
                }
                else
                {
                    string fieldName = child["left"].Value<string>("field");
                    if (string.IsNullOrEmpty(fieldName)) throw new ArgumentNullException("left field");
                    string op = child.Value<string>("op");
                    IWhereItem whereItem;
                    switch (op)
                    {
                        case "like":
                            whereItem = new WhereItem_Like(child.Value<string>("right"));
                            break;
                        case "equal":
                            whereItem = new WhereItem_Eq(child.Value<string>("right"));
                            break;
                        case "not_equal":
                            whereItem = new WhereItem_NotEq(child.Value<string>("right"));
                            break;
                        case "is_empty":
                            whereItem = new WhereItem_Custom("${field} is null");
                            break;
                        case "is_not_empty":
                            whereItem = new WhereItem_Custom("${field} is not null");
                            break;
                        case "less":
                            whereItem = new WhereItem_Custom("${field} < {0}", child.Value<string>("right"));
                            break;
                        case "less_or_equal":
                            whereItem = new WhereItem_Custom("${field} <= {0}", child.Value<string>("right"));
                            break;
                        case "greater":
                            whereItem = new WhereItem_Custom("${field} > {0}", child.Value<string>("right"));
                            break;
                        case "greater_or_equal":
                            whereItem = new WhereItem_Custom("${field} >= {0}", child.Value<string>("right"));
                            break;
                        case "between":
                            if (child["right"] == null) throw new ArgumentNullException("right");
                            object[] rightArr = child["right"].ToObject<object[]>();
                            whereItem = new WhereItem_Between(rightArr[0], rightArr[1]);
                            break;
                        case "not_between":
                            if (child["right"] == null) throw new ArgumentNullException("right");
                            object[] rightArrNot = child["right"].ToObject<object[]>();
                            whereItem = new WhereItem_Custom("(${field} < {0} or ${field} > {1} )", rightArrNot);
                            break;
                        default:
                            throw new ClientException(op + "不支持！");
                    }
                    var conditionItem = new QueryParamConditionItem(fieldName, whereItem);
                    conditionGroup.Items.Add(conditionItem);
                }
            }
            return conditionGroup;
        }

        [TestMethod]
        public void TestAMIS()
        {
            var _OEMProgram = new OEMProgram("JPAAS");
            _OEMProgram.Run(null);
            Context = _OEMProgram.CreateDBContext();
            QueryParam queryParam = new QueryParam() { entityType = typeof(DesignPage), queryField = "id,name,conf,parent_id,key_code,update_time" };

            queryParam.AddWhere("key_code", "appIndex");
            // queryParam.WhereIn("id",new object[]{1,2,3});
            var page = Context.DoQuery(queryParam);
            Assert.IsTrue(page.Rows.Count > 0);

            // Assert.IsTrue(Context.GetDBAccess().GetLastSql().IndexOf("(key_code = @key_code1) and ((deleted <> @deleted) or (deleted is null))") > 0);

            var sln = "apps";
            var data = Context.QuerySingle<PermissionSlutionDBData>(new { OEM = Context.OEM.Name, SLN__Custom = QQZiFramework.WhereItem_Custom.Create("$field = {0} or Permission ={0}", sln) });
            Assert.IsNotNull(data);

            _OEMProgram = new OEMProgram("JPAAS-WS");
            _OEMProgram.Run(null);
            Context = _OEMProgram.CreateDBContext();

            queryParam = new QueryParam()
            {
                entityType = typeof(EmptyEntity),
                queryField = "id,flow_name",
                permission = "noAuth-worksheet",
                orderBy = "id"
            };
            queryParam.AddWhere("ids", "1,2,3").AddWhere("id_LIKE","55");
            queryParam.Page=new Pagger{PageIndex=1,PageSize=20};
            var ws = Context.DoQuery(queryParam);
            queryParam = new QueryParam()
            {
                entityType = typeof(EmptyEntity),
                queryField = "CreateAll,disposeIntimeAll",
                permission = "index_summary",
                orderBy = "id"
            };
            queryParam.AddWhere("date_BETWEEN", "2024-06-07|2024-08-30");
            queryParam.AddWhere("dept_id","1");
            ws = Context.DoQuery(queryParam);
            Context.LogMessage("汇总记录："+ws.Rows.Count);
            Assert.IsNotNull(ws);
        }

        [TestMethod]
        public void TestOracleVariables()
        {
            string connectionString = "User Id=jpaas;Password=123456;Data Source=192.168.2.127:1521/ORCLPDB1";

            // 需要查询的值
            string searchValue = "apps"; // 使用相同的值

            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                connection.Open();

                // // 查询 id 或 name 字段
                // string query = "select Id as \"Id\",SLN,OEM,Builder as \"QueryBuilderString\",WhereObject as \"WhereObjectString\",Status as \"Status\",Permission as \"Permission\",QueryField as \"QueryField\",GroupByFields as \"GroupByFields\",EntityName as \"EntityName\",IsTree as \"IsTree\" from amis_sln_definition where (OEM = :OEM1) and (SLN = :SLN1 or Permission =:SLN1) and ((Status != :STATUS1) or (Status is null)) ";
                // using (OracleCommand command = new OracleCommand(query, connection))
                // {
                //     // 添加一个参数
                //     command.Parameters.Add(new OracleParameter("OEM1",OracleDbType.Varchar2, "JPAAS", ParameterDirection.Input));
                //     command.Parameters.Add(new OracleParameter("SLN1", OracleDbType.Varchar2, "apps", ParameterDirection.Input));
                //     command.Parameters.Add(new OracleParameter("STATUS1", 1));
                //     Assert.IsTrue(ExecuteQuery(command) > 0);
                // }
                // SQL 查询语句
                string query = @"SELECT * FROM amis_sln_definition WHERE   (SLN = :SLN1 or Permission =:SLN1) and ((Status <> :STATUS1) or (Status is null)) ";

                using (OracleCommand command = new OracleCommand(query, connection))
                {
                    // 添加参数绑定，STATUS 是数字类型
                    command.Parameters.Add(new OracleParameter("SLN1", OracleDbType.Varchar2, "apps", ParameterDirection.Input));
                    command.Parameters.Add(new OracleParameter("SLN1", OracleDbType.Varchar2, "apps", ParameterDirection.Input));
                    command.Parameters.Add(new OracleParameter("STATUS", OracleDbType.Int32, 1, ParameterDirection.Input));
                    ExecuteQuery(command);
                }
            }
        }

        static int ExecuteQuery(OracleCommand command)
        {
            int i = 0;
            using (OracleDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    i++;
                    Console.WriteLine($"ID: {reader["id"]}, SLN: {reader["SLN"]}");
                }
            }
            return i;
        }


        [TestMethod]
        public void TestInsert()
        {

            // Dictionary<string, object> rowValues = new Dictionary<string, object>();
            // rowValues.Add("update_time", DateTime.Now);
            // rowValues.Add("parent_id", 0);
            // rowValues.Add("app_id", 100);
            // rowValues.Add("deleted", true);
            // rowValues.Add("tenant_id", 199);
            // rowValues.Add("name", "测试");
            // var entity = EmptyEntity.CreateIncrForSave("design_pages", "id", null, rowValues);
            // Context.DoSaveNew(entity);   
            DesignPage designPage = new DesignPage() { app_id = 100, deleted = true, tenant_id = 199, name = "测试", parent_id = 0, update_time = DateTime.Now };
            Context.DoSaveNew(designPage);
            designPage.conf = "this is a 测试";
            Context.Update(designPage, "conf");
            Context.DoDelete<DesignPage>(designPage.id.ToString());
            Context.ExecuteCommand("delete from design_pages where id=" + designPage.id);
        }


        [TestMethod]
        public void TestBulkInsert()
        {
            // List<DesignPage> list = new List<DesignPage>();
            // for (var i = 0; i < 3000; i++)
            // {
            //     DesignPage shopCategory = new DesignPage() { update_time= DateTime.Now, parent_id =0, app_id=100, name = "测试" + i ,deleted =true, tenant_id =1};
            //     list.Add(shopCategory);
            // }
            // Context.DoBulkInsert(list);
            List<EmptyIncrEntity> list = new List<EmptyIncrEntity>();
            for (var i = 0; i < 3000; i++)
            {
                Dictionary<string, object> rowValues = new Dictionary<string, object>();
                rowValues.Add("update_time", DateTime.Now);
                rowValues.Add("parent_id", 0);
                rowValues.Add("app_id", 100);
                rowValues.Add("deleted", 1);
                rowValues.Add("tenant_id", 199);
                rowValues.Add("name", "测试" + i);
                rowValues.Add("KEY_CODE", "KEY_CODE_" + i);
                var entity = EmptyEntity.CreateIncrForSave("design_pages", "id", null, rowValues);
                list.Add(entity);
            }
            Context.DoBulkInsert(list);

        }

        [TestMethod]
        public void TestConstraint()
        {
            var tables = Context.GetDBAccess().GetTables();
            Assert.IsTrue(tables.Count > 0);
            var fks = Context.GetDBAccess().GetTableForeignKeys("ACT_RU_TASK");
            Assert.IsTrue(fks.Length > 0);
            var pks = Context.GetDBAccess().GetTablePrimaryKeys("ACT_RU_TASK");
            Assert.IsTrue(pks[0] == "ID_");
        }

        [TestMethod]
        public void TestParseSql()
        {
             var _OEMProgram = new OEMProgram("JPAAS");
            _OEMProgram.Run(null);
            Context = _OEMProgram.CreateDBContext();
            QueryParam queryParam = new QueryParam() { entityType = typeof(DesignPage), queryField = "id,name,conf,parent_id,key_code,update_time" };
            
            queryParam.AddWhere("key_code", "appIndex");
            IDbDataParameter[] parameters ;
            var sql = Context.ParseQuerySql(queryParam, out parameters);
            Console.WriteLine(sql[0]);
            Assert.IsTrue(sql.Length == 1);
            Assert.IsTrue(parameters.Length > 0 && parameters[0].ParameterName.IndexOf("key_code") > 0 && parameters[0].Value.Equals( "appIndex"));

            queryParam.Page=new Pagger(){ PageIndex= 1, PageSize=10 };       
            sql = Context.ParseQuerySql(queryParam, out parameters);     
            Assert.IsTrue(sql.Length == 2);
            Console.WriteLine(sql[0]);
            Console.WriteLine(sql[1]);
            Assert.IsTrue(parameters.Length > 0 && parameters[0].ParameterName.IndexOf("key_code") > 0 && parameters[0].Value.Equals( "appIndex"));
            Context.DoQuery(queryParam);
            
        }

    }
}